package dao;
import entity.materialBean;
import entity.Page;
import utils.DBUtil;
import utils.DBUtil_Plus;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class materialDAO {
    /**
     * 查询满足条件的材料列表
     * @param  name 材料名称
     * @param  id 材料编号
     * @param type 材料类型
     * @return List<materialBean>--符合条件的任务列表
     */

    public List<materialBean> findMaterial(String name,String id,String type,Page page){
        List<materialBean> materialBeanList=new ArrayList<materialBean>();
        int num;
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;

        try{
            // 获取数据库连接
            conn = DBUtil_Plus.getConnection();
            stmt = conn.createStatement();

            // 构建基础的条件查询 SQL 语句
            String baseQuery = "SELECT * FROM t_material WHERE 1=1";
            if (name != null && !name.isEmpty()) {
                baseQuery += " AND name LIKE '%" + name + "%'";
            }
            if (id != null && !id.isEmpty()) {
                baseQuery += " AND id LIKE '%" + id + "%'";
            }
            if (type != null && !type.isEmpty()) {
                baseQuery += " AND type = '" + type + "'";
            }

            // 查询满足条件的总记录数
            String countQuery = "SELECT CEIL(COUNT(*)/?) AS totalPages FROM (" + baseQuery + ") AS sub";
            pstmt = conn.prepareStatement(countQuery);
            pstmt.setInt(1, page.getPageSize());
            rs = pstmt.executeQuery();

            // 提取总页数
            if (rs.next()) {
                page.setTotalPage(rs.getInt("totalPages"));
            }

            // 添加排序和分页
            String sql = baseQuery + " ORDER BY id LIMIT " + page.getCurrentPageTotalRows() + ", " + page.getPageSize();

            // 显示序号初始化
            stmt.execute("SET @row_number = 0");
            String numberedQuery = "SELECT @row_number:=@row_number + 1 AS `row_number`, t.* FROM (" + sql + ") t";

            // 执行查询
            rs = stmt.executeQuery(numberedQuery);
            num = page.getCurrentPageTotalRows()+1;

            // 遍历结果集
            while (rs.next()) {
                materialBean obj = new materialBean();
                obj.setNum(num);
                obj.setR_id(rs.getInt("r_id"));
                obj.setName(rs.getString("name"));
                obj.setId(rs.getString("id"));
                obj.setType(rs.getString("type"));
                obj.setManufacturer(rs.getString("manufacturer"));
                obj.setProductiondate(rs.getString("productiondate"));
                obj.setFactorysite(rs.getString("factorysite"));
                obj.setContacts(rs.getString("contacts"));
                obj.setTelephone(rs.getString("telephone"));
                obj.setStatement(rs.getString("statement"));
                materialBeanList.add(obj);
                num++;
            }

        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            if (conn != null) {
                DBUtil_Plus.closeConnection(conn);
            }
        }
        return materialBeanList;


    }

    public materialBean editMaterial(int key) {
        materialBean materialBeanObj = new materialBean();

        // Ensure that resources are closed properly using try-with-resources
        try (Connection conn = DBUtil_Plus.getConnection()) {
            if (conn == null) {
                throw new RuntimeException("Database connection is null");
            }

            String sql = "SELECT * FROM t_material WHERE r_id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, key);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        materialBeanObj.setR_id(rs.getInt("r_id"));
                        materialBeanObj.setName(rs.getString("name"));
                        materialBeanObj.setId(rs.getString("id"));
                        materialBeanObj.setType(rs.getString("type"));
                        materialBeanObj.setManufacturer(rs.getString("manufacturer"));
                        materialBeanObj.setProductiondate(rs.getString("productiondate"));
                        materialBeanObj.setFactorysite(rs.getString("factorysite"));
                        materialBeanObj.setContacts(rs.getString("contacts"));
                        materialBeanObj.setTelephone(rs.getString("telephone"));
                        materialBeanObj.setStatement(rs.getString("statement"));
                    } else {
                        setDefaultMaterialBean(materialBeanObj);
                    }
                }
            }

            DBUtil_Plus.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException("Database error occurred", e);
        }

        return materialBeanObj;
    }
    private void setDefaultMaterialBean(materialBean bean) {
        bean.setR_id(-1); // Default or null value
        bean.setName("");
        bean.setId("");
        bean.setType("");
        bean.setManufacturer("");
        bean.setProductiondate("");
        bean.setFactorysite("");
        bean.setContacts("");
        bean.setTelephone("");
        bean.setStatement("");
    }

    public static int deleteMaterialById(int recordId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int ret=-1;
        try {
//            conn = DBUtil_Plus.getConnection();
            conn = DBUtil_Plus.getConnection();

            // 创建 SQL 删除语句
            String deleteQuery = "DELETE FROM t_material WHERE r_id = ?";

            // 使用 PreparedStatement 预编译 SQL 语句
            pstmt = conn.prepareStatement(deleteQuery);
            pstmt.setInt(1, recordId); // 设置参数

            // 执行删除操作
            ret = pstmt.executeUpdate();
            return ret;
        } catch (SQLException e) {
            e.printStackTrace(); // 可以将异常抛出或者记录到日志中
        } finally {
            // 关闭连接和语句对象
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    DBUtil_Plus.closeConnection(conn);
                }
            } catch (SQLException e) {
                e.printStackTrace(); // 可以将异常抛出或者记录到日志中
            }
        }
        return ret;
    }
    public  void insertMaterial(materialBean obj) {

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {

//            conn = DBUtil_Plus.getConnection();
            conn = DBUtil_Plus.getConnection();


            String sql = "INSERT INTO t_material(name, id, type, manufacturer, productiondate, factorysite, contacts, telephone, statement) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, obj.getName());
            pstmt.setString(2, obj.getId());
            pstmt.setString(3, obj.getType());
            pstmt.setString(4, obj.getManufacturer());
            pstmt.setString(5, obj.getProductiondate());
            pstmt.setString(6, obj.getFactorysite());
            pstmt.setString(7, obj.getContacts());
            pstmt.setString(8, obj.getTelephone());
            pstmt.setString(9, obj.getStatement());

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(); // 可以将异常抛出或者记录到日志中
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    DBUtil_Plus.closeConnection(conn);
                }
            } catch (SQLException e) {
                e.printStackTrace(); // 可以将异常抛出或者记录到日志中
            }
        }
    }


    public  boolean updateMaterial(materialBean material) {
        boolean isSuccess = false;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {

//            conn = DBUtil_Plus.getConnection();
             conn = DBUtil_Plus.getConnection();


            String sql = "UPDATE t_material SET name=?, id=?, type=?, manufacturer=?, productiondate=?, factorysite=?, contacts=?, telephone=?, statement=? WHERE r_id=?";

            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, material.getName());
            pstmt.setString(2, material.getId());
            pstmt.setString(3, material.getType());
            pstmt.setString(4, material.getManufacturer());
            pstmt.setString(5, material.getProductiondate());
            pstmt.setString(6, material.getFactorysite());
            pstmt.setString(7, material.getContacts());
            pstmt.setString(8, material.getTelephone());
            pstmt.setString(9, material.getStatement());
            pstmt.setInt(10, material.getR_id());

            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                isSuccess = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    DBUtil_Plus.closeConnection(conn);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return isSuccess;
    }

}
